using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;
using DTN.Core.Common;
using DTN.Core.Utilities;
namespace com.dtn.deals
{
    public class tbl_CityManager : Itbl_CityManager
    {
        private static Itbl_CityManager itbl_CityManager = null;
        private tbl_CityManager()
        {
        }
        public static Itbl_CityManager CreateInstance()
        {
            if (itbl_CityManager == null)
            {
                itbl_CityManager = new tbl_CityManager();
            }
            return itbl_CityManager;
        }
        public int Inserttbl_City(tbl_City tbl_city)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@cityName", SqlDbType.NVarChar),
																new SqlParameter("@cParent", SqlDbType.Int),
																new SqlParameter("@isDel", SqlDbType.TinyInt),
																new SqlParameter("@cDate", SqlDbType.DateTime),
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tbl_city.cityName;
            parameters[2].Value = tbl_city.cParent;
            parameters[3].Value = tbl_city.isDel;
            parameters[4].Value = tbl_city.cDate;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_City", CommandType.StoredProcedure, parameters);
                tbl_city.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tbl_city.ID = -1;
            }
            return tbl_city.ID;
        }

        public int Inserttbl_Area(tbl_City tbl_city)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@cityId", SqlDbType.Int),
																new SqlParameter("@name", SqlDbType.NVarChar),
																new SqlParameter("@cDate", SqlDbType.DateTime),
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tbl_city.ID;
            parameters[2].Value = tbl_city.NameArea;
            parameters[3].Value = tbl_city.cDate;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_Area", CommandType.StoredProcedure, parameters);
                tbl_city.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tbl_city.ID = -1;
            }
            return tbl_city.ID;
        }

        public bool Updatetbl_Area(tbl_City tbl_city)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@cityId", SqlDbType.Int),
																new SqlParameter("@name", SqlDbType.NVarChar),
																new SqlParameter("@cDate", SqlDbType.DateTime),
															};
            parameters[0].Value = tbl_city.AreaID;
            parameters[1].Value = tbl_city.ID;
            parameters[2].Value = tbl_city.NameArea;
            parameters[3].Value = tbl_city.cDate;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_Area", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public bool Updatetbl_City(tbl_City tbl_city)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@cityName", SqlDbType.NVarChar),
																new SqlParameter("@cParent", SqlDbType.Int),
																new SqlParameter("@isDel", SqlDbType.TinyInt),
																new SqlParameter("@cDate", SqlDbType.DateTime),
															};
            parameters[0].Value = tbl_city.ID;
            parameters[1].Value = tbl_city.cityName;
            parameters[2].Value = tbl_city.cParent;
            parameters[3].Value = tbl_city.isDel;
            parameters[4].Value = tbl_city.cDate;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_City", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public bool Deletetbl_City(int id)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Deletetbl_City", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public bool Deletetbl_Area(int id)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Deletetbl_Area", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public tbl_City Selecttbl_CityByID(int id)
        {
            IDataReader dr;
            tbl_City tbl_city = new tbl_City();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                dr = dataProvider.ExecuteReader("sp_Selecttbl_City", CommandType.StoredProcedure, parameters);
                while (dr.Read())
                {
                    tbl_city.ID = Convert.ToInt32(dr["cityID"].ToString());
                    tbl_city.cityName = (dr["cityName"].ToString());
                    tbl_city.cParent = Convert.ToInt32(dr["cParent"].ToString());
                    tbl_city.isDel = Convert.ToInt32(dr["isDel"].ToString());
                    tbl_city.cDate = Convert.ToDateTime(dr["cDate"].ToString());
                }

            }
            catch (Exception ex)
            {
                return null;
            }
            return tbl_city;
        }
        public DataTable Selecttbl_CitiesDynamic(string Where, string Order)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Where;
            parameters[1].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_CitiesDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        public DataTable Selecttbl_AreaDynamic(string Where, string Order)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Where;
            parameters[1].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_AreaDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        public DataTable Selecttbl_CityWithPageDynamic(int StartIndex, int MaxRecords, string Where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_CityWithPagesDynamic", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        public DataTable Selecttbl_AreaWithPageDynamic(int StartIndex, int MaxRecords, string Where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_AreaWithPagesDynamic", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
        //Cuong Code
        public static DataTable GetAllDistrictByParent(int parentcity)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_City_GetAllDistrictByParent", dbConn);
            dbCmd.Parameters.AddWithValue("@ParentId", parentcity);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static string GetNameById(int _id)
        {
            string title = "";
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_City_GetNameById", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", _id);
            try
            {
                dbConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                object returnRole = dbCmd.ExecuteScalar();
                if (returnRole != null && returnRole != DBNull.Value)
                    title = ConvertUtility.ToString(returnRole);
            }
            finally
            {
                dbConn.Close();
            }
            return title;
        }
        public static string GetNameParent(int _id)
        {
            string title = "";
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_City_GetNameParent", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", _id);
            try
            {
                dbConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                object returnRole = dbCmd.ExecuteScalar();
                if (returnRole != null && returnRole != DBNull.Value)
                    title = ConvertUtility.ToString(returnRole);
            }
            finally
            {
                dbConn.Close();
            }
            return title;
        }
        public static int GetParentId(int _id)
        {
            int retVal = -1;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_City_GetParentId", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", _id);
            try
            {
                dbConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                object returnRole = dbCmd.ExecuteScalar();
                if (returnRole != null && returnRole != DBNull.Value)
                    retVal = ConvertUtility.ToInt32(returnRole);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
    }
}
